package cj.web.lkg.dao;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;

@DAO
public interface SurveyNewsDAO {
	
	@SQL("SELECT * FROM SurveyPeople WHERE id =:1 ")
	List<Map> querySurveyPeopleById(int id);
	
	@SQL("SELECT id FROM SurveyDepotSubject WHERE deleteFlag=1 AND surveyDepotId =:1 AND isMutil =:2 ")
	List<Integer> querySurveyDepotSubjectIdById(int surveyDepotId,int isMutil);
	
	@SQL("UPDATE SurveyPeople SET subjectIds=:1 WHERE id =:3 AND surveyId=:2 ")
	void updateSubjectIdsBySurveyId(String subjectIds, int surveyId, int surveyPeopleId);
	
	@SQL("UPDATE SurveyPeople SET answersIds=:1 WHERE id =:2 ")
	void updateAnswersIdsBySurveyId(String answersIds, int surveyPeopleId);
	
	@SQL("SELECT id,surveyDepotId,subject,isMutil,count,answers FROM SurveyDepotSubject WHERE deleteFlag=1 AND id in (:ids) AND surveyDepotId=:2 ")
	List<Map> querySurveyDepotSubjectByInIds(@SQLParam("ids") List<String> ids,int surveyDepotId);
	
	@SQL("SELECT id,surveyDepotId,subject,isMutil,count,answers FROM SurveyDepotSubject WHERE deleteFlag=1 AND id in (:ids) ")
	List<Map> querySurveyDepotSubjectByInIds(@SQLParam("ids") List<String> ids);
	
	@SQL("SELECT subjectDepotId FROM SurveyDepotItem WHERE subjectDepotId IN (:subjectDepotIds) AND id NOT IN (:ids) AND answer =1 AND surveyDepotId =:3 GROUP BY subjectDepotId ")
	List<Integer> querySurveySubjectId(@SQLParam("subjectDepotIds") List<String> subjectDepotIds,@SQLParam("ids") List<String> ids,int surveyDepotId);
	
	@SQL("SELECT subjectDepotId FROM SurveyDepotItem WHERE subjectDepotId IN (:subjectDepotIds) AND id NOT IN (:ids) AND answer =1 GROUP BY subjectDepotId ")
	List<Integer> querySurveySubjectId(@SQLParam("subjectDepotIds") List<String> subjectDepotIds,@SQLParam("ids") List<String> ids);
	
	@SQL("SELECT * FROM SurveyDepotItem WHERE deleteFlag=1 AND subjectDepotId=:1 AND answer=1 ")
	List<Map> querySurveyDepotItemById(int subjectId);
	
	@SQL("SELECT * FROM SurveyDepotSubject WHERE deleteFlag=1 AND id IN (:ids) ORDER BY id ")
	List<Map> querySurveyDepotSubjectById(@SQLParam("ids") List<Integer> ids);
	
	@SQL("SELECT * FROM SurveyDepotSubject WHERE deleteFlag=1 AND id IN (:ids) AND surveyDepotId=:2 ORDER BY id ")
	List<Map> querySurveyDepotSubjectById(@SQLParam("ids") List<Integer> ids,int surveyDepotId);
		
	//查找考卷说明
	@SQL("SELECT id,name,descs,choiceCount,choiceFraction,judgementCount,judgementFraction,multiSelectCount,multiSelectFraction,surveyDepotId,voteStart,voteEnd,ksAt FROM Survey WHERE deleteFlag=1 AND id =:1 ")
	List<Map> querySurveyById(int id);
	
	//查找考卷题
	@SQL("SELECT id FROM SurveyDepotSubject WHERE deleteFlag=1 AND surveyDepotId =:1 AND isMutil =:2 ")
	List<Integer> queryDepotSubjectById(int surveyDepotId,int isMutil);
	
	//查找考卷项
	@SQL("SELECT id,surveyDepotId,subjectDepotId,item,answer,orderNum FROM SurveyDepotItem WHERE deleteFlag=1 AND surveyDepotId =:1 AND subjectDepotId =:2 ")
	List<Map> querySurveyDepotItemById(int surveyDepotId,int subjectDepotId);
	
	@SQL("SELECT id,surveyDepotId,subjectDepotId,item,answer,orderNum FROM SurveyDepotItem WHERE deleteFlag=1 AND subjectDepotId =:1 ")
	List<Map> querySurveyDepotItemById4DepotId(int subjectDepotId);
	
	//查找考卷项answer=1
	@SQL("SELECT id,surveyDepotId,subjectDepotId,item,answer,orderNum FROM SurveyDepotItem WHERE deleteFlag=1 AND surveyDepotId =:1 AND subjectDepotId =:2 AND answer=1 ")
	List<Map> querySurveyDepotItemById41(int surveyDepotId,int subjectDepotId);
	
	//查找考卷题
	@SQL("SELECT * FROM SurveyPeople WHERE deleteFlag=1 AND phone =:1 AND surveyId=:2 ")
	List<Map> querySurveyPeopleByPhone(String phone,int surveyId);
	
	//参加考题人信息
	@SQL("UPDATE SurveyPeople SET name=:2,phone=:3,companyGroup=:4 WHERE id =:1 AND surveyId=:5 ")
	void updateSurveyPeopleByPhone(int surveyPeopleId,String name, String phone, String companyGroup,int surveyId);
	
	//插入参与人员信息
	@ReturnGeneratedKeys
    @SQL("INSERT INTO SurveyPeople (id,surveyId,name,phone,companyGroup,deleteFlag,createUser,createAt,updateUser,updateAt) VALUES ( :1, :2, :3, :4, :5, 1,:6, now(), :6, now()) ")
    int createSurveyPeople(Integer id,int surveyId, String name, String phone, String companyGroup, String createUser);

	//更新个人分数
	@SQL("UPDATE SurveyPeople SET score=:2,updateAt=now(),errorIds=:3,deleteFlag=0 WHERE id =:1 ")
	void updateSurveyPeople(int id,int score,String errorIds);
	
	//查询组合试卷ID
	@SQL("SELECT * FROM SurveyGroup WHERE deleteFlag=1 AND id =:1 ")
	List<Map> querySurveyGroupById(int id);
	
	//--------------
	@SQL("SELECT * FROM SurveyPeopleLogin WHERE deleteFlag=1 AND phone=:1 ")
	List<Map> querySurveyPeopleLoginByPhone(String phone);
	
	//查询组合试卷ID
	@SQL("SELECT * FROM SurveyGroup WHERE deleteFlag=1 AND id IN (:ids) ")
	List<Map> querySurveyGroupByIds(@SQLParam("ids") List<String> ids);
	
	@SQL("SELECT a.*,b.name as surveyGroupName FROM SurveyPeople a left join SurveyGroup b on a.surveyId=b.id WHERE a.deleteFlag=0 AND a.phone =:1 ")
	List<Map> querySurveyPeopleByPhone(String phone);
}